import pyodbc

from flask import Flask, jsonify
from flask_cors import CORS

app = Flask(__name__)
CORS(app)


def execute_query(query_string):
    # 设置数据库连接参数
    conn_params = {
        'Driver': '{MySQL ODBC 8.0 Unicode Driver}',
        'Server': 'localhost',
        'Database': 'world',
        'UID': 'root',
        'PWD': '123456'
    }

    # 创建连接对象
    conn = pyodbc.connect(
        ';'.join([f"{key}={value}" for key, value in conn_params.items()]))

    # 创建游标对象
    cursor = conn.cursor()

    # 执行查询
    cursor.execute(query_string)

    # 获取查询结果的列名
    columns = [column[0] for column in cursor.description]

    # 获取查询结果的所有行数据
    rows = cursor.fetchall()

    # 将查询结果转换成列表，每行数据作为一个字典
    result_set = [dict(zip(columns, row)) for row in rows]

    # 关闭游标和连接
    cursor.close()
    conn.close()

    # 返回查询结果
    return result_set


@app.route('/')
def hello():
    return jsonify({'message': 'Hello, world!'})


@app.route("/cncity", methods=['GET'])
def getCnCity():
    stmt = "select * from v_cncity"
    rst = execute_query(stmt)

    return jsonify(rst)

# get => select
# put => update
# post => insert
# delete => delete


@app.route('/cncity/<string:cityname>', methods=['GET'])
def getCnCityP(cityname):
    rst = execute_query(f'''
select
	concat(district_letter, city_letter) as plate_head
from
	v_cncity
where
    city_name = '{cityname}'
                        ''')

    return jsonify(rst)


if __name__ == '__main__':
    app.run()
